﻿using System;
using System.Collections.Generic;
using System.Linq;
using orienta.Models;
using orienta.ViewModels;

namespace orienta.Helper
{

    public class DependenciaInfo
    {
        public decimal IdDep {get; set; }
        public decimal? IdDepPadre {get; set; }
        public string DescDep {get; set; }
        public decimal CodDep { get; set; }
        public string DescDepPadre { get; set; }
        public string FechaInicio { get; set; }
        public string FechaFin { get; set; }
        public decimal Seleccionado { get; set; }
    }

    public class DependenciaHelper
    {
        private GoodTireEntities db = new GoodTireEntities();
        public int _CATEGORIA_DEPENDENCIA_REGION = 319;

        public List<DependenciaInfo> getDetalleDependenciaDescList(int CodRegion)
        {
            var detalleList = (from 
                               MaeDep in db.ORIMaeDependencia// on DetDep.nIdeDep equals MaeDep.nIdeDep
                               orderby MaeDep.nCodDep 
                               //where (MaeDep.nIdeDepRegion == CodRegion)
                               select new DependenciaInfo() { IdDep = MaeDep.nIdeDep, IdDepPadre = MaeDep.nIdeDepPadre, DescDep = MaeDep.vDescripcion, CodDep = MaeDep.nCodDep });
            return detalleList.ToList();
        }

        public List<DependenciaInfo> getDetalleDependenciaRegionDescList(int CodRegion)
        {
            var detalleList = (from
                               MaeDep in db.ORIMaeDependencia// on DetDep.nIdeDep equals MaeDep.nIdeDep
                               orderby MaeDep.nCodDep
                               where (MaeDep.nIdeDepRegion == CodRegion || MaeDep.nIdeDep == CodRegion)
                               select new DependenciaInfo() { IdDep = MaeDep.nIdeDep, IdDepPadre = MaeDep.nIdeDepPadre, DescDep = MaeDep.vDescripcion, CodDep = MaeDep.nCodDep, Seleccionado = MaeDep.vBanSel});
            return detalleList.ToList();
        }


        public List<DependenciaInfo> getDetalleDependenciaDescListRegion(decimal CodRegion)
        {
            var detalleList = (from
                               MaeDep in db.ORIMaeDependencia// on DetDep.nIdeDep equals MaeDep.nIdeDep
                               orderby MaeDep.nCodDep
                               where (MaeDep.nIdeDepRegion == CodRegion || 0 == CodRegion)
                               select new DependenciaInfo() { IdDep = MaeDep.nIdeDep, IdDepPadre = MaeDep.nIdeDepPadre, DescDep = MaeDep.vDescripcion, CodDep = MaeDep.nCodDep });
            return detalleList.ToList();
        }


        public List<DependenciaInfo> getDetalleDependenciaRegionList()
        {
            var detalleList = (from MaeDep in db.ORIMaeDependencia// on DetDep.nIdeDep equals MaeDep.nIdeDep
                               orderby MaeDep.nCodDep
                               where (MaeDep.nIdeTipoDep == _CATEGORIA_DEPENDENCIA_REGION) 
                               select new DependenciaInfo() { IdDep = MaeDep.nIdeDep, IdDepPadre = MaeDep.nIdeDepPadre, DescDep = MaeDep.vDescripcion, CodDep = MaeDep.nCodDep });
            return detalleList.ToList();
        }

        public List<Dependencia> getDetalleDependenciaUnicaList(int iddep)
        {
            var detalleList = (from MaeDep in db.ORIMaeDependencia
                               join MaeDepPadre in db.ORIMaeDependencia 
                               on MaeDep.nIdeDepPadre equals MaeDepPadre.nIdeDep
                               into DepPadreDesc
                               from DepPadre in DepPadreDesc.DefaultIfEmpty() 
                               orderby MaeDep.nCodDep
                               where (MaeDep.nIdeDep == iddep)
                               select new Dependencia() {
                                    sIdDep = MaeDep.nIdeDep,
                                    sIdDepPadre = MaeDep.nIdeDepPadre,
                                    sDescripcion = MaeDep.vDescripcion,
                                    sCodigo = MaeDep.nCodDep,
                                    sFecInic = MaeDep.dtFecIniVig,
                                    sFecFin = MaeDep.dtFecFinVig,
                                    depPadre = DepPadre.vDescripcion
                               });
            return detalleList.ToList();
        }

        public List<Dependencia> getDetalleDependenciaPadreList(int iddeppadre)
        {
            var detalleList = (from MaeDep in db.ORIMaeDependencia
                               join MaeDepPadre in db.ORIMaeDependencia on MaeDep.nIdeDepPadre equals MaeDepPadre.nIdeDep
                               into DepPadreDesc
                               from DepPadre in DepPadreDesc.DefaultIfEmpty() 
                               orderby MaeDep.nCodDep
                               where (MaeDep.nIdeDepPadre == iddeppadre || MaeDep.nIdeDep == iddeppadre)
                               select new Dependencia()
                               {
                                   sIdDep = MaeDep.nIdeDep,
                                   sIdDepPadre = MaeDep.nIdeDepPadre,
                                   sDescripcion = MaeDep.vDescripcion,
                                   sCodigo = MaeDep.nCodDep,
                                   sFecInic = MaeDep.dtFecIniVig,
                                   sFecFin = MaeDep.dtFecFinVig,
                                   depPadre = DepPadre.vDescripcion
                               });
            return detalleList.ToList();
        }

        public List<Dependencia> getDetalleDependenciaConsultaList(string strConsulta)
        {
            var detalleList = (from MaeDep in db.ORIMaeDependencia
                               join MaeDepPadre in db.ORIMaeDependencia on MaeDep.nIdeDepPadre equals MaeDepPadre.nIdeDep
                               into DepPadreDesc
                               from DepPadre in DepPadreDesc.DefaultIfEmpty() 
                               orderby MaeDep.nCodDep
                               where (MaeDep.vDescripcion.Contains(strConsulta))
                               select new Dependencia()
                               {
                                   sIdDep = MaeDep.nIdeDep,
                                   sIdDepPadre = MaeDep.nIdeDepPadre,
                                   sDescripcion = MaeDep.vDescripcion,
                                   sCodigo = MaeDep.nCodDep,
                                   sFecInic = MaeDep.dtFecIniVig,
                                   sFecFin = MaeDep.dtFecFinVig,
                                   depPadre = DepPadre.vDescripcion
                               });
            return detalleList.ToList();
        }

        public decimal updateDependenciaRegion(decimal idDep)
        {
            String sql = "UPATE ORIMaeDependencia SET nIdeDepRegion=" + idDep + " where nIdeDep=" + idDep;
            var max = db.ExecuteStoreCommand(sql);
            return 1;
        }

        public decimal getIdDependencia()
        {
            String sql = "select isnull(max(nIdeDep),0) + 1 from ORIMaeDependencia";
            var max = db.ExecuteStoreQuery<Decimal>(sql);
            List<Decimal> valores = max.ToList<Decimal>();
            Decimal secuencia = valores.First<Decimal>();

            return secuencia;
        }

        public int getNumPersonas(string strNumDoc, int intTipoDoc)
        {
            String sql = "select count(*) from ORIMaePersona WHERE nIdeTipoDocum=" + intTipoDoc + " and vNumDocum='" + strNumDoc + "'";
            var queryResult = db.ExecuteStoreQuery<int>(sql);
            int num_personas = queryResult.FirstOrDefault();
            return num_personas;
        }

        /*
        public decimal getCorrelativoSolicitud(decimal idDep)
        {
            String sql = "select isnull(max(nidesolicitud),0) + 1 from ORIMaeSolicitud where nIdeDep=" + idDep;
            var max = db.ExecuteStoreQuery<Decimal>(sql);
            List<Decimal> valores = max.ToList<Decimal>();
            Decimal secuencia = valores.First<Decimal>();

            return idDep + secuencia;
        }

        public decimal getSeqPasoEvento(decimal nIdSolicitud)
        {
            String sql = "select isnull(max(nSeqPaso),0) + 1 from ORIDetEventos where nIdSolicitud=" + nIdSolicitud;
            var max = db.ExecuteStoreQuery<Decimal>(sql);
            List<Decimal> valores = max.ToList<Decimal>();
            Decimal secuencia = valores.First<Decimal>();

            return secuencia;
        }*/

    }

}